# -*- coding:utf-8 -*-

import uuid


sqlDict = {
    # 检索未完成任务
    "select_task_undown":            """SELECT t.id, t.need, t.targetId, t.taskState, t.taskNum, t.createDate, t.updateDate, l.state
                                        FROM task as t
                                            LEFT JOIN lampClient as l
                                            ON t.targetId = l.lcId
                                        WHERE t.taskState = 0 or t.taskState = 1;""",

    # 检索单开任务的灯端的基站id(目标id-目标数量
    "select_task_baseStation":       """SELECT t.id, t.need, t.targetId, l.baId
                                        FROM task as t
                                            LEFT JOIN %s as l
                                            ON t.targetId = l.%s
                                        WHERE (t.taskState = 0 or t.taskState = 1 or t.taskState = 3) AND l.%s IN (%s);""",

    # 检索待启动定时任务
    "select_taskPlan_needwork":      """SELECT id, fixingId, runMode, scratchLine, distance, command, now_state
                                        FROM taskPlan
                                        WHERE to_days(NOW()) between to_days(bDate) and to_days(aDate)
                                            AND HOUR(scratchLine) < date_add(now(), interval 1 hour)
                                            AND now_state = 0;""",

    # 指定路段处于bell模式中的任务
    "select_task_bellMode":          """SELECT id
                                        FROM task
                                        WHERE targetId IN ('%s') and need = '%s' and taskState = 3;""",

    # 基站id是否已注册
    "select_baseStation_register":   """SELECT count(*) as num
                                        FROM baseStation 
                                        WHERE baId = '%s';""",

    # 基站所属路段
    "select_baseStation_queenAnt":   """SELECT sectionId FROM baseStation WHERE baId = '%s'""",

    # 路段所有基站
    "select_baseStation_soldierAnt": """SELECT baId FROM baseStation WHERE sectionId IN (%s)""",

    # 根据任务id检索对应的基站
    # "selectBaseStation_lcMaster":    """SELECT t.need, l.lcId, b.baId
    #                                     FROM task as t
    #                                         LEFT JOIN lampClient as l
    #                                         ON t.targetId = l.lcId
    #                                         LEFT JOIN baseStation as b
    #                                         ON l.baId = b.baId
    #                                     WHERE t.id IN (%s)""",


    # "select_brightness_antGain":     """SELECT (select datas from brightness WHERE baId = ba.baId GROUP BY createDate desc limit 0,1) as data
    #                                     FROM baseStation as ba
    #                                     WHERE ba.sectionId =
    #                                         (SELECT sectionId FROM baseStation WHERE baId='%s')
    #                                          AND (state = 0 or state = 1)""",

    # 获取一个路段所有灯端最近一段时间的光感值
    "select_brightness_antGain": """SELECT left(CAST(AVG(br.datas) AS CHAR), 5) AS d
                                    FROM (SELECT baId, datas FROM brightness WHERE createDate > date_sub(now(), interval 10 MINUTE )) as br
                                    WHERE br.baId IN (
                                                (SELECT baId 
                                                FROM baseStation 
                                                WHERE sectionId = '%s' 
                                                    AND (state = 0 or state = 1))
                                                );
                                    """,

    # 获取一个所有指定灯端的目前状态
    "select_lampClient_state":       """SELECT lcId, state 
                                        FROM lampClient 
                                        WHERE lcId IN (%s)""",

    # 获取一个基站下所有路灯的开/关数量
    "select_lampClient_switchNum":   """SELECT count(*) FROM lampClient WHERE baId = '%s' AND state = 0
                                        UNION
                                        SELECT count(*) FROM lampClient WHERE baId= '%s' AND state = 1;""",

    # 获取一个路段的基站下，所有非白名单的路灯的当前状态
    "select_lampClient_ergateState": """SELECT lcId
                                        FROM lampClient 
                                        WHERE sectionId = '%s' AND baId = '%s' AND state = '%s' AND whiteList = 0;""",

    # 存储基站光温湿信息
    "insert_daily_storage":          """INSERT INTO %s (baId, datas, createDate) VALUES ('%s', '%s', CURRENT_TIMESTAMP())""",


    # 每次开关存入监控
    "insert_onofListen_switch":      """INSERT INTO onofListen (lcId, baId, switch, ha, user) 
                                        VALUES ('%s', '%s', '%s', '%s', '%s');""",

    # 更新功耗统计
    "replace_power":                 """set @d = '%s';
                                        set @lcId = '%s';
                                        set @po = %s;
                                        set @var = (SELECT p.power 
                                                    FROM (select lcId, power from power where createDate = @d) as p
                                                    WHERE p.lcId = @lcId);
                                        REPLACE INTO power (lcId, power, createDate) 
                                        VALUES ( @lcId
                                                , CASE WHEN @var is null
                                                  THEN @po
                                                  ELSE @var + @po
                                                  END
                                                , @d);""",


    # 指定路段的开关任务如果已存在则更新，否则插入
    "insert_update_bellMode":        """INSERT INTO task (id, need, targetId, taskState, taskNum, updateDate)
                                        VALUES (
                                          (SELECT t.id 
                                           FROM (
                                             SELECT id 
                                             FROM task 
                                             WHERE targetId = '%s' AND taskState = 3)
                                             AS t
                                          ), %s, '%s', 0, 0, CURRENT_TIMESTAMP())
                                        ON duplicate KEY
                                        UPDATE taskState = %s;""",

    # 存储任务，如果存在更新，否则插入
    # "insert_task":                   """INSERT into task (id, need, targetId, taskState, taskNum, updateDate)
    #                                     select t.id, %s, t.targetId, 0, 0, current_timestamp()
    #                                       from (select * from task where targetId IN (%s) and taskState = 3) as t
    #                                     on duplicate key
    #                                     update taskState = 1;""",


    # # 存储任务（需要拼接）
    "insert_task":                   """INSERT INTO task (command, taskState, taskNum, updateDate)
                                        VALUES """,

    # 存入故障
    "insert_fault":                  """INSERT INTO fault (fixingId, fixingType)
                                        VALUES """,

    # 灯端初始化/断电重连，与基站从属更新
    "update_lampClient_init":        """UPDATE lampClient SET baId = '%s', state = '0' WHERE lcId = '%s'""",

    # 更新灯端状态
    "update_lampClient_state":       """UPDATE lampClient SET state = '%s' WHERE lcId = '%s'""",

    # 更新灯端状态为故障（批量更新）
    "update_lampClient_fault":       """UPDATE lampClient SET state = 3 WHERE lcId IN (%s)""",

    # 更新任务执行状态(任务进程中)
    "update_task":                   """UPDATE task
                                        SET taskState = '%s'
                                            ,taskNum = (taskNum + 1)
                                            ,updateDate = CURRENT_TIMESTAMP()
                                        WHERE id IN (%s)""",

    # bell模式中更新任务状态
    "update_task_bellMode":         """UPDATE task
                                       SET taskState = '%s'
                                          , updateDate = CURRENT_TIMESTAMP()
                                       WHERE id IN (%s)"""
}



